package com.common.autocode.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;

public class DbMetaData {
	private static String MYSQL_CLASS = "com.mysql.jdbc.Driver";
	private static String url;
	private static String username;
	private static String password;

	private static Map<Integer, String> typeToName;

	static {
		typeToName = new HashMap<Integer, String>();
		typeToName.put(Types.ARRAY, "ARRAY"); //$NON-NLS-1$
		typeToName.put(Types.BIGINT, "BIGINT"); //$NON-NLS-1$
		typeToName.put(Types.BINARY, "BINARY"); //$NON-NLS-1$
		typeToName.put(Types.BIT, "BIT"); //$NON-NLS-1$
		typeToName.put(Types.BLOB, "BLOB"); //$NON-NLS-1$
		typeToName.put(Types.BOOLEAN, "BOOLEAN"); //$NON-NLS-1$
		typeToName.put(Types.CHAR, "CHAR"); //$NON-NLS-1$
		typeToName.put(Types.CLOB, "CLOB"); //$NON-NLS-1$
		typeToName.put(Types.DATALINK, "DATALINK"); //$NON-NLS-1$
		typeToName.put(Types.DATE, "DATE"); //$NON-NLS-1$
		typeToName.put(Types.DECIMAL, "DECIMAL"); //$NON-NLS-1$
		typeToName.put(Types.DISTINCT, "DISTINCT"); //$NON-NLS-1$
		typeToName.put(Types.DOUBLE, "DOUBLE"); //$NON-NLS-1$
		typeToName.put(Types.FLOAT, "FLOAT"); //$NON-NLS-1$
		typeToName.put(Types.INTEGER, "INTEGER"); //$NON-NLS-1$
		typeToName.put(Types.JAVA_OBJECT, "JAVA_OBJECT"); //$NON-NLS-1$
		typeToName.put(Types.LONGVARBINARY, "LONGVARBINARY"); //$NON-NLS-1$
		typeToName.put(Types.LONGVARCHAR, "LONGVARCHAR"); //$NON-NLS-1$
		typeToName.put(Types.NCHAR, "NCHAR"); //$NON-NLS-1$
		typeToName.put(Types.NCLOB, "NCLOB"); //$NON-NLS-1$
		typeToName.put(Types.NVARCHAR, "NVARCHAR"); //$NON-NLS-1$
		typeToName.put(Types.LONGNVARCHAR, "LONGNVARCHAR"); //$NON-NLS-1$
		typeToName.put(Types.NULL, "NULL"); //$NON-NLS-1$
		typeToName.put(Types.NUMERIC, "NUMERIC"); //$NON-NLS-1$
		typeToName.put(Types.OTHER, "OTHER"); //$NON-NLS-1$
		typeToName.put(Types.REAL, "REAL"); //$NON-NLS-1$
		typeToName.put(Types.REF, "REF"); //$NON-NLS-1$
		typeToName.put(Types.SMALLINT, "SMALLINT"); //$NON-NLS-1$
		typeToName.put(Types.STRUCT, "STRUCT"); //$NON-NLS-1$
		typeToName.put(Types.TIME, "TIME"); //$NON-NLS-1$
		typeToName.put(Types.TIMESTAMP, "TIMESTAMP"); //$NON-NLS-1$
		typeToName.put(Types.TINYINT, "TINYINT"); //$NON-NLS-1$
		typeToName.put(Types.VARBINARY, "VARBINARY"); //$NON-NLS-1$
		typeToName.put(Types.VARCHAR, "VARCHAR"); //$NON-NLS-1$
	}

	public void setDbInfo(String url, String username, String password) {
		this.url = url;
		this.username = username;
		this.password = password;
	}

	public Connection getConnection() throws SQLException {
		try {
			Class.forName(MYSQL_CLASS);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return DriverManager.getConnection(url, username, password);
	}

	public static void main(String[] args) throws SQLException {
		DbMetaData d = new DbMetaData();
		d.setDbInfo("jdbc:mysql://localhost:3306/snaker-web?useUnicode=true&characterEncoding=utf-8", "root", "root");
		// d.getColumnTypes(d.getConnection(), "t_park_info");

		List<ColumnData> data = d.getMysqlTablePdmCloumns("snaker-web","sec_role");
		for(ColumnData cd : data){
			System.out.println(cd);
		}
			List<ChildColumnData> child = d.getChildTablePdmCloumns("snaker-web","sec_role");
			System.out.println("=========1======= "+child.size());
	}

	private Map getColumnTypes(Connection con, String tableName) throws SQLException {
		Map map = new HashMap();
		String sql = "select * from " + tableName + " where 1>2";
		PreparedStatement ps = con.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		ResultSetMetaData rsmd = rs.getMetaData();
		int numberOfColumns = rsmd.getColumnCount();
		for (int i = 1; i <= numberOfColumns; i++) {
			map.put(rsmd.getColumnName(i).toLowerCase(), rsmd.getColumnType(i));
//			System.out.println(rsmd.getColumnName(i).toLowerCase() + " : " + rsmd.getColumnClassName(i) + " : "
//					+ rsmd.getColumnTypeName(i) + " : " + rsmd.getColumnType(i));
		}
		rs.close();
		ps.close();
		return map;
	}

	/**
	 * oracle 获取表信息
	 * 
	 * @param Table
	 * @param Owner
	 * @throws SQLException
	 */
	public List<ColumnData> getMysqlTablePdmCloumns(String schema,String table) throws SQLException {
		Connection con = getConnection();

		Map<String, String> cloumnTypes = getColumnTypes(con, table);
		List columnList = new ArrayList();
		try {
			Statement stmt = con.createStatement();
			String sql = "select COLUMN_COMMENT 'Comment', COLUMN_NAME 'Code',NUMERIC_PRECISION 'Precision',"
					+ "NUMERIC_SCALE 'Scale',CHARACTER_MAXIMUM_LENGTH 'Length',IS_NULLABLE 'Nullable',"
					+ "DATA_TYPE 'DataType',case when COLUMN_KEY='PRI' then 'TRUE' else 'FALSE' end 'Primary',a.TABLE_COMMENT "
					+ "from information_schema.columns t left join information_schema.TABLES a "
					+ "on t.TABLE_SCHEMA = a.TABLE_SCHEMA and t.TABLE_NAME = a.TABLE_NAME "
					+ "where t.TABLE_SCHEMA='"+schema+"' and t.TABLE_NAME='"+table+"'";

			System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String name = rs.getString("Code").toLowerCase();
				String columnType = typeToName.get(cloumnTypes.get(name));
				String type = rs.getString("DataType");
				String comment = rs.getString("Comment");
				String precision = rs.getString("Precision");
				String scale = rs.getString("Scale");
				String charmaxLength = (rs.getString("Length") == null) ? "" : rs.getString("Length");
				String nullable = TableConvert.getNullAble(rs.getString("Nullable"));
				String primary = rs.getString("Primary");
				type = getType(type, charmaxLength, scale);
				String tableComment = rs.getString("TABLE_COMMENT");

				ColumnData cd = new ColumnData();
				cd.setColumnName(name);
				cd.setDataType(type);
				cd.setColumnType(columnType);
				cd.setColumnComment(comment);
				cd.setPrecision(charmaxLength);
				cd.setScale(scale);
				cd.setCharmaxLength(charmaxLength);
				cd.setNullable(nullable);
				cd.setPrimary(primary);
				cd.setTableComment(tableComment);
				cd.setTableName(table);
				columnList.add(cd);

			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			con.close();
			return columnList;
		}
	}

	public List<ChildColumnData> getChildTablePdmCloumns(String schema,String table) throws SQLException{
		List<ChildColumnData> list = new ArrayList();
		Connection con = getConnection();
		Statement stmt = con.createStatement();
		String sql = "select t.TABLE_NAME,t.COLUMN_NAME,t.REFERENCED_TABLE_NAME,t.REFERENCED_COLUMN_NAME from information_schema.KEY_COLUMN_USAGE t where t.REFERENCED_TABLE_SCHEMA='"+schema+"' and t.REFERENCED_TABLE_NAME='"+table+"'";
		System.out.println(sql);
		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next()) {
			String talbeName = rs.getString("TABLE_NAME");
			String columnName = rs.getString("COLUMN_NAME");
			String reftableName = rs.getString("REFERENCED_TABLE_NAME");
			String refcolumnName = rs.getString("REFERENCED_COLUMN_NAME");
			
			ChildColumnData ccd = new ChildColumnData();
			ccd.setTableName(talbeName);
			ccd.setColumnName(columnName);
			ccd.setReftableName(reftableName);
			ccd.setRefcolumnName(refcolumnName);
			list.add(ccd);
		}
		rs.close();
		stmt.close();
		con.close();
		/*if(!temp.isEmpty()){
			list = new ArrayList();
			for(String tableName : temp){
				list.add(getMysqlTablePdmCloumns(schema,tableName));
			}
		}*/
		return list;
	}
	
	public String getType(String dataType, String precision, String scale) {
		dataType = dataType.toLowerCase();
		if (dataType.contains("char")) {
			if ((StringUtils.isNotBlank(precision)) && (Integer.parseInt(precision) == 1))
				dataType = "java.lang.Boolean";
			else
				dataType = "java.lang.String";
		} else if (dataType.contains("int"))
			dataType = "java.lang.Integer";
		else if (dataType.contains("float"))
			dataType = "java.lang.Float";
		else if (dataType.contains("double"))
			dataType = "java.lang.Double";
		else if (dataType.contains("number"))
			if ((StringUtils.isNotBlank(scale)) && (Integer.parseInt(scale) > 0))
				dataType = "java.math.BigDecimal";
			else if ((StringUtils.isNotBlank(precision)) && (Integer.parseInt(precision) > 6))
				dataType = "java.lang.Long";
			else
				dataType = "java.lang.Integer";

		else if (dataType.contains("decimal"))
			dataType = "java.math.BigDecimal";
		else if (dataType.contains("date"))
			dataType = "java.util.Date";
		else if (dataType.contains("time"))
			dataType = "java.util.Date";
		else if (dataType.contains("clob"))
			dataType = "java.sql.Clob";
		else
			dataType = "java.lang.Object";

		return dataType;
	}
}
